package com.etc.dao;

import com.etc.pojo.EmpMst;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Repository
public class ADao {

    @Autowired
    DataSource dataSource;

    public List<HashMap> findALL() {


        List<HashMap> list = new ArrayList<HashMap>();
        // 3. 创建执行器statement
        Statement stmt = null;
        try {
            Connection conn = dataSource.getConnection();
            stmt = conn.createStatement();

            // 4. 执行SQL
            // 不要有分号；
            String sql = "select * from emp_mst";
            ResultSet rs = stmt.executeQuery(sql);



            while (rs.next()) {
                HashMap hm = new HashMap();

                int emp_id = rs.getInt("id");
                String emp_name = rs.getString("emp_name");
                String password = rs.getString("password");
                String username = rs.getString("user_name");
                String sex = rs.getString(5);
                int emp_age = rs.getInt(6);
                hm.put("emp_id", emp_id);
                hm.put("password", password);
                hm.put("username", username);
                hm.put("sex", sex);
                hm.put("emp_age", emp_age);
                list.add(hm);
            }


            // 5. 关闭资源
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    public EmpMst findByAAAName(String name) {
        try {
            EmpMst empMst = new EmpMst();
            Connection conn = dataSource.getConnection();

            String sql = "select * from emp_mst where emp_name= ? ";
            // 防止SQL注入，用？占位
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, name);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {

                empMst.setEmpName(rs.getString("emp_name"));
                empMst.setPassword(rs.getString("password"));
                empMst.setAge(rs.getInt("age"));
                empMst.setUsername(rs.getString("user_name"));
                empMst.setSex(rs.getString("sex"));
                empMst.setId(rs.getInt("id"));

;

            }


            // 5. 关闭资源
            conn.close();
            return empMst;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public int insert(EmpMst emp) {
        try {
            EmpMst empMst = new EmpMst();
            Connection conn = dataSource.getConnection();

            String sql = " INSERT INTO `emp_mst` VALUES (null, ?, ?, ?, ?, ?) ";
            // 防止SQL注入，用？占位
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, emp.getEmpName());
            ps.setString(2, emp.getPassword());
            ps.setString(3, emp.getUsername());
            ps.setString(4, emp.getSex());
            ps.setInt(5, empMst.getAge());

            int r = ps.executeUpdate();



            // 5. 关闭资源
            conn.close();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

    }
}
